/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package application;

import application.beans.DiscussionBean;
import application.beans.MessageBean;
import java.util.ArrayList;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;

/**
 *
 * @author Julien
 */
public class ForumDAO 
{
    Section chercherSection(java.lang.Integer id)
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        
        return (Section)(session.load(Section.class, id));
    }
    
    SousSection chercherSousSection(java.lang.Integer id)
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        
        return (SousSection)(session.load(SousSection.class, id));
    }
    
    Discussion chercherDiscussion(java.lang.Integer id)
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        
        return (Discussion) (session.load(Discussion.class, id));
    }
    
    List rechercherDiscussionsParTitre(String recherche)
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        
        List temp = new ArrayList();
        Query query = session.createSQLQuery("SELECT * FROM discussion WHERE titre LIKE :recherche").addEntity(application.Discussion.class);
        query.setParameter("recherche", "%"+recherche+"%");
        
        temp = query.list();
        return temp;
    }
    
    List rechercherDiscussionsParTexte(String recherche)
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        
        StringBuilder strQuery = new StringBuilder(200);
        List resultats = new ArrayList();
        List temp = new ArrayList();
        Query query = session.createSQLQuery("SELECT fk_id_discussion FROM messages WHERE message LIKE :recherche");
        query.setParameter("recherche", "%"+recherche+"%");
        temp = query.list();
        
        for(int i = 0; i < temp.size(); i++)
        {
            if(i!=0)
            {
                strQuery.append(", ");
            }
            strQuery.append(temp.get(i));
        }
        
        query = session.createSQLQuery("SELECT * FROM discussion WHERE id_discussion IN ("+strQuery.toString()+")").addEntity(application.Discussion.class);
        
        resultats = query.list();
        return resultats;
    }
    
    List rechercherDiscussions(String recherche)
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        
        StringBuilder strQuery = new StringBuilder(200);
        List resultats = new ArrayList();
        List temp = new ArrayList();
        Query query = session.createSQLQuery("SELECT fk_id_discussion FROM messages WHERE message LIKE :recherche");
        query.setParameter("recherche", "%"+recherche+"%");
        temp = query.list();
        
        for(int i = 0; i < temp.size(); i++)
        {
            if(i!=0)
            {
                strQuery.append(", ");
            }
            strQuery.append(temp.get(i));
        }
        
        query = session.createSQLQuery("SELECT * FROM discussion WHERE titre LIKE :recherche " + ((temp.isEmpty())?"":"OR id_discussion IN ("+strQuery.toString()+")")).addEntity(application.Discussion.class);
        query.setParameter("recherche", "%"+recherche+"%");
        
        resultats = query.list();
        return resultats;
    }
    
    List obtenirListeSections()
    {   
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        
        List temp = new ArrayList();
        temp = session.createSQLQuery("SELECT * FROM section").addEntity(application.Section.class).list();
        
        return temp;
    }
    
    List obtenirListeDiscussions()
    {        
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        
        List temp = new ArrayList();
        temp = session.createSQLQuery("SELECT * FROM discussion").addEntity(application.Discussion.class).list();
        
        return temp;
    }
    
    List obtenirRecents()
    {        
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        
        List temp = new ArrayList();
        temp = session.createSQLQuery("SELECT * FROM discussion order by temps_dernier_message desc limit 10").addEntity(application.Discussion.class).list();
        
        return temp;
    }
    
    void ecrireSection(String titre) 
    {        
      Session session = HibernateUtil.getSessionFactory().getCurrentSession();
      session.beginTransaction();  
      /*
       * Faite à l'ancienne, car on utilise des clés naturelles et que hibernate 
       * ne supoporte pas les clés naturelles de manière naturelle
       */    
        Query query = session.createSQLQuery("insert into section"
                + "(id_section, titre, nb_sous_sections)"
                + " values (0, :1, 0)");

        query.setParameter("1", titre);
        query.executeUpdate();
        //on commit le tout dans la base de données
        session.getTransaction().commit();
    }
    
    void ecrireSousSection(String titre, String description, int idSection) 
    {
            
      Session session = HibernateUtil.getSessionFactory().getCurrentSession();
      session.beginTransaction();  
      /*
       * Faite à l'ancienne, car on utilise des clés naturelles et que hibernate 
       * ne supoporte pas les clés naturelles de manière naturelle
       */    
        Query query = session.createSQLQuery("insert into sous_section"
                + "(id_sous_section, titre, sousTitre, nb_discussion, fk_id_section)"
                + " values (0, :1, :2, 0, :3)");

        query.setParameter("1", titre);
        query.setParameter("2", description);
        query.setParameter("3", idSection);
        query.executeUpdate();
        //on commit le tout dans la base de données
        session.getTransaction().commit();
    }
    
    int ecrireDiscussion(DiscussionBean discussion, String strIdSousSection, String cours) 
    {        
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();    
      
        Query query;
        if(cours == null || cours.equals(""))
        {
            query = session.createSQLQuery("insert into discussion"
                + "(id_discussion, titre, nb_messages, nb_vues, temps_dernier_message, dernier_user, fk_id_sous_section, id_cours, verrou)"
                + " values (0, :1, :2, :3, NOW(), :4, :5, NULL, :7)");
        }
        else
        {
            query = session.createSQLQuery("insert into discussion"
                + "(id_discussion, titre, nb_messages, nb_vues, temps_dernier_message, dernier_user, fk_id_sous_section, id_cours, verrou)"
                + " values (0, :1, :2, :3, NOW(), :4, :5, :6, :7)");
            query.setParameter("6", cours);
        }
        query.setParameter("1", discussion.getTitre());
        query.setParameter("2", discussion.getNbMessages());
        query.setParameter("3", discussion.getNbVues());
        query.setParameter("4", discussion.getDernierUser().getIdCompte());
        query.setParameter("5", strIdSousSection);
        query.setParameter("7", discussion.getVerrou());
        query.executeUpdate();
        
        List temp = session.createSQLQuery("select id_discussion from discussion"
             + " where dernier_user = " 
             + discussion.getDernierUser().getIdCompte() 
             + " order by temps_dernier_message desc limit 1").list();
        
        session.getTransaction().commit();
        
        return (Integer)temp.get(0);
    }
    
    void incrementerVues(int idDiscussion)
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();    
      
        Query query = session.createSQLQuery("update discussion set nb_vues "
                + "= nb_vues + 1 WHERE id_discussion = " + idDiscussion);
        query.executeUpdate();
        session.getTransaction().commit();
    }
    
    void incrementerMessage(int idDiscussion)
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();    
      
        Query query = session.createSQLQuery("update discussion set nb_messages"
                + " = nb_messages + 1 WHERE id_discussion = " + idDiscussion);
        query.executeUpdate();
        session.getTransaction().commit();
    }
    
    void decrementerMessage(int idDiscussion)
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();    
      
        Query query = session.createSQLQuery("update discussion set nb_messages"
                + " = nb_messages - 1 WHERE id_discussion = " + idDiscussion);
        query.executeUpdate();
        session.getTransaction().commit();
    }
    
    void incrementerDiscussion(int idSousSection)
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();    
      
        Query query = session.createSQLQuery("update sous_section set nb_discussions"
                + " = nb_discussions + 1 WHERE id_sous_section = " + idSousSection);
        query.executeUpdate();
        session.getTransaction().commit();
    }
    
    void decrementerDiscussion(int idSousSection)
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();    
      
        Query query = session.createSQLQuery("update sous_section set nb_discussions"
                + " = nb_discussions - 1 WHERE id_sous_section = " + idSousSection);
        query.executeUpdate();
        session.getTransaction().commit();
    }

    void ecrireMessage(MessageBean message, int idDiscussion) 
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();  
        /*
         * Faite à l'ancienne, car on utilise des clés naturelles et que hibernate 
         * ne supoporte pas les clés naturelles de manière naturelle
         */    
        Query query = session.createSQLQuery("insert into messages"
                + "(id_message, date, message, fk_id_discussion, fk_id_compte)"
                + " values (0, NOW(), :1, :2, :3)");

        query.setParameter("1", message.getMessage());
        query.setParameter("2", idDiscussion);
        query.setParameter("3", message.getCompte().getIdCompte().intValue());
        query.executeUpdate();
        //on commit le tout dans la base de données
        session.getTransaction().commit();
    }
    
    void deplacerSousSection(int idSousSection, int idSection)
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();  
        
        Query query = session.createSQLQuery("update sous_section set "
                + "fk_id_section = :1 where id_sous_section = :2");
        
        query.setParameter("1", idSection);
        query.setParameter("2", idSousSection);
        query.executeUpdate();
        
        session.getTransaction().commit();
    }
    
    void deplacerDiscussion(int idDiscussion, int idSousSection)
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();  
        
        Query query = session.createSQLQuery("update discussion set "
                + "fk_id_sous_section = :1 where id_discussion = :2");
        
        query.setParameter("1", idSousSection);
        query.setParameter("2", idDiscussion);
        query.executeUpdate();
        
        session.getTransaction().commit();
    }
    
    void verrouillerDiscussion(int idDiscussion, Boolean verrou)
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();  
        
        Query query = session.createSQLQuery("update discussion set "
                + "verrou = :1 where id_discussion = :2");
        
        query.setParameter("1", verrou.booleanValue());
        query.setParameter("2", idDiscussion);
        query.executeUpdate();
        
        session.getTransaction().commit();
    }
    
    void supprimerDiscussion(int idDiscussion)
    {
        supprimerMessageParDiscussion(idDiscussion);
        
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();  
        
        Query query = session.createSQLQuery("delete from discussion where "
                + "id_discussion = :1");
        query.setParameter("1", idDiscussion);
        query.executeUpdate();
        
        session.getTransaction().commit();
    }
    
    void supprimerMessageParDiscussion(int idDiscussion)
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();  
        
        Query query = session.createSQLQuery("delete from messages where "
                + "fk_id_discussion = :1");
        
        query.setParameter("1", idDiscussion);
        query.executeUpdate();
        
        session.getTransaction().commit();
    }
    
    void supprimerMessage(int idMessage)
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();  
        
        Query query = session.createSQLQuery("delete from messages where "
                + "id_message = :1");
        
        query.setParameter("1", idMessage);
        query.executeUpdate();
        
        session.getTransaction().commit();
    }
    
    void supprimerSousSection(int idSousSection)
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();  
        
        Query query = session.createSQLQuery("delete from sous_section where "
                + "id_sous_section = :1");
        
        query.setParameter("1", idSousSection);
        query.executeUpdate();
        
        session.getTransaction().commit();
    }
    
    void supprimerSection(int idSection)
    {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();  
        
        Query query = session.createSQLQuery("delete from section where "
                + "id_section = :1");
        
        query.setParameter("1", idSection);
        query.executeUpdate();
        
        session.getTransaction().commit();
    }
}

